package dbaccess;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;

import collect.StockPairs;
import collect.MarketPairs;

/**
 * Utility class for database access and update operations
 * @author Yevgen
 */

public abstract class dbInteract {

	/* Working database name & preset authentication set */
	private static final String user = "root";
	private static final String password = "mypass";
	private static final String dbname = "stockdb";

	/**
	 * Inserts an entry into STOCKS table; Input arguments: stock new sid, stock
	 * symbol and stock name
	 * @throws ClassNotFoundException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 */

	dbInteract() throws InstantiationException, IllegalAccessException, ClassNotFoundException{
		Class.forName("com.mysql.jdbc.Driver").newInstance();
	}
	
	public static void dbInsert(int sid, String symbol, String name) {

		Connection conn;

		try {
			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://localhost/" + dbname;
			conn = DriverManager.getConnection(url, user, password);

			System.out.print("\n[Performing INSERT] ... ");
			try {

				String sql = "INSERT INTO stocks " + "VALUES (?, ?, ?)";
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setInt(1, sid);
				ps.setString(2, symbol);
				ps.setString(3, name);
				ps.executeUpdate();

			} catch (SQLException ex) {
				System.err.println(ex.getMessage());
			}

			conn.close();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
	}

	/**
	 * Inserts an entry from STOCKS table; Input argument specifies a particular
	 * stock by its sid
	 */

	public static void dbDelete(int sid) {

		Connection conn;

		try {
			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://localhost/" + dbname;
			conn = DriverManager.getConnection(url, user, password);

			System.out.print("\n[Performing DELETE] ... ");
			try {

				String sql = "DELETE FROM stocks WHERE SID = ?";
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setInt(1, sid);
				ps.executeUpdate();

			} catch (SQLException ex) {
				System.err.println(ex.getMessage());
			}

			conn.close();

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
	}

	/**
	 * Update an entry in STOCKS table; Input argument specifies a particular
	 * stock by its sid
	 */

	public static void dbUpdate(int sid) {

		Connection conn;

		try {
			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://localhost/" + dbname;
			conn = DriverManager.getConnection(url, user, password);

			System.out.print("\n[Performing UPDATE] ... ");
			try {
				String sql = "UPDATE stocks SET NAME = 'Updated Company Inc.' WHERE SID = ?";
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setInt(1, sid);
				ps.executeUpdate();

			} catch (SQLException ex) {
				System.err.println(ex.getMessage());
			}

			conn.close();

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
	}

	/**
	 * Select an entry in STOCKS table; Input argument specifies a particular
	 * stock by its symbol
	 */

	public static void dbSelect(String symbol) {

		Connection conn;

		try {
			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://localhost/" + dbname;
			conn = DriverManager.getConnection(url, user, password);

			System.out.print("\n[Performing SELECT] ... ");
			try {

				String sql = "SELECT SID, SYMBOL, NAME FROM stocks WHERE SYMBOL = \""
						+ symbol + "\"";
				Statement st = conn.createStatement();

				ResultSet rs = st.executeQuery(sql);
				System.out.println();
				while (rs.next()) {
					int rs_sid = rs.getInt("SID");
					String rs_symbol = rs.getString("SYMBOL");
					String rs_name = rs.getString("NAME");
					System.out.println(rs_sid + "   " + rs_symbol + "   "
							+ rs_name);
				}

			} catch (SQLException ex) {
				System.err.println(ex.getMessage());
			}

			conn.close();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
	}

	/**
	 * Perform a direct action (insert, delete, update) on the database; Input
	 * argument is an in-line SQL statement
	 */

	public static void dbDirectAction(String sql) {

		Connection conn;

		try {
			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://localhost/" + dbname;
			conn = DriverManager.getConnection(url, user, password);

			System.out.print("\n[Performing DIRECT] ... ");
			try {
				Statement st = conn.createStatement();
				st.executeUpdate(sql);

			} catch (SQLException ex) {
				System.err.println(ex.getMessage());
			}

			conn.close();

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
	}

	/**
	 * Loads all stocks available in the database into a stocks linked list for
	 * further operations and manipulations
	 */

	public static void dbLoadStockPairs(LinkedList<StockPairs> ll) {

		Connection conn;

		try {
			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://localhost/" + dbname;
			conn = DriverManager.getConnection(url, user, password);

			System.out.print("[Performing dbLoadStockPairs] ... \n");
			try {

				String sql = "SELECT SYMBOL, NAME FROM stocks";
				Statement st = conn.createStatement();

				ResultSet rs = st.executeQuery(sql);

				while (rs.next()) {
					String rs_symbol = rs.getString("SYMBOL");
					String rs_name = rs.getString("NAME");
					ll.add(new StockPairs(rs_name, rs_symbol));
				}

			} catch (SQLException ex) {
				System.err.println(ex.getMessage());
			}

			conn.close();

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
	}

	/**
	 * Stores stock's current market values into the database Input specifies
	 * stock ID, its current price, volume and change indicators
	 */
	public static void dbInsertCurrStock(int sid, Double price, int volume,
			Double schange) {

		Connection conn;

		try {
			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://localhost/" + dbname;
			conn = DriverManager.getConnection(url, user, password);

			System.out.print("\n[Performing dbInsertCurrStock] ... ");
			try {

				java.util.Date today = new java.util.Date();
				java.sql.Timestamp curr_time = new java.sql.Timestamp(
						today.getTime());

				String sql = "INSERT INTO current_stock "
						+ "VALUES (?, ?, ?, ?, ?)";
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setInt(1, sid);
				ps.setTimestamp(2, curr_time);
				ps.setDouble(3, price);
				ps.setInt(4, volume);
				ps.setDouble(5, schange);
				ps.executeUpdate();

			} catch (SQLException ex) {
				System.err.println(ex.getMessage());
			}

			conn.close();

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}

	}

	/**
	 * Stores stock's current market values into the database Input specifies
	 * stock ID, its current price, volume and change indicators
	 */
	public static void dbInsertFinalStock(int sid, Double open,
			Double close, Double high, Double low, int volume, Double schange,
			Double pe, Double eps) {

		Connection conn;

		try {
			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://localhost/" + dbname;
			conn = DriverManager.getConnection(url, user, password);

			System.out.print("\n[Performing dbInsertFinalStock] ... ");
			try {

				java.util.Date today = new java.util.Date();
				java.sql.Date date = new java.sql.Date(today.getTime());

				String sql = "INSERT INTO stock_details "
						+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setInt(1, sid);
				ps.setDate(2, date);
				ps.setDouble(3, open);
				ps.setDouble(4, close);
				ps.setDouble(5, high);
				ps.setDouble(6, low);
				ps.setInt(7, volume);
				ps.setDouble(8, schange);
				ps.setDouble(9, pe);
				ps.setDouble(10, eps);
				ps.executeUpdate();

			} catch (SQLException ex) {
				System.err.println(ex.getMessage());
			}

			conn.close();

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}

	}

	/**
	 * Finds stock entry in the database, based on the input symbol Returns the
	 * corresponding stock ID
	 */

	public static int dbGetSID(String symbol) {

		Connection conn;

		try {

			int rs_sid = 0;

			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://localhost/" + dbname;
			conn = DriverManager.getConnection(url, user, password);

			try {

				String sql = "SELECT SID FROM stocks WHERE SYMBOL = \""
						+ symbol + "\"";
				Statement st = conn.createStatement();

				ResultSet rs = st.executeQuery(sql);

				while (rs.next()) {
					rs_sid = rs.getInt("SID");
				}

			} catch (SQLException ex) {
				System.err.println(ex.getMessage());
				return 0;
			}

			conn.close();
			return rs_sid;

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
			return 0;
		}
	}
	
	/**
	 * Loads all stocks available in the database into a stocks linked list for
	 * further operations and manipulations
	 */

	public static void dbLoadIndexPairs(LinkedList<MarketPairs> ll) {

		Connection conn;

		try {
			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://localhost/" + dbname;
			conn = DriverManager.getConnection(url, user, password);

			System.out.print("[Performing dbLoadIndexPairs] ... \n");
			try {

				String sql = "SELECT SYMBOL, NAME FROM indices";
				Statement st = conn.createStatement();

				ResultSet rs = st.executeQuery(sql);

				while (rs.next()) {
					String rs_symbol = rs.getString("SYMBOL");
					String rs_name = rs.getString("NAME");
					ll.add(new MarketPairs(rs_name, rs_symbol));

				}

			} catch (SQLException ex) {
				System.err.println(ex.getMessage());
			}

			conn.close();

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
	}

	/**
	 * Stores stock's current market values into the database Input specifies
	 * stock ID, its current price, volume and change indicators
	 */
	public static void dbInsertCurrIndex(int iid, Double value,	Double ichange) {

		Connection conn;

		try {
			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://localhost/" + dbname;
			conn = DriverManager.getConnection(url, user, password);

			System.out.print("\n[Performing dbInsertCurrIndex] ... ");
			try {

				java.util.Date today = new java.util.Date();
				java.sql.Timestamp curr_time = new java.sql.Timestamp(
						today.getTime());

				String sql = "INSERT INTO current_index "
						+ "VALUES (?, ?, ?, ?)";
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setInt(1, iid);
				ps.setTimestamp(2, curr_time);
				ps.setDouble(3, value);
				ps.setDouble(4, ichange);
				ps.executeUpdate();

			} catch (SQLException ex) {
				System.err.println(ex.getMessage());
			}

			conn.close();

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}

	}

	/**
	 * Stores stock's current market values into the database Input specifies
	 * stock ID, its current price, volume and change indicators
	 */
	public static void dbInsertFinalIndex(int sid, Double open, Double close,  Double ichange) {

		Connection conn;

		try {
			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://localhost/" + dbname;
			conn = DriverManager.getConnection(url, user, password);

			System.out.print("\n[Performing dbInsertFinalIndex] ... ");
			try {

				java.util.Date today = new java.util.Date();
				java.sql.Date date = new java.sql.Date(today.getTime());

				String sql = "INSERT INTO index_details "
						+ "VALUES (?, ?, ?, ?, ?)";
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setInt(1, sid);
				ps.setDate(2, date);
				ps.setDouble(3, open);
				ps.setDouble(4, close);
				ps.setDouble(5, ichange);
				ps.executeUpdate();

			} catch (SQLException ex) {
				System.err.println(ex.getMessage());
			}

			conn.close();

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}

	}
	
	/**
	 * Finds market entry in the database, based on the input symbol Returns the
	 * corresponding market ID
	 */

	public static int dbGetIID(String symbol) {

		Connection conn;

		try {

			int rs_iid = 0;

			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://localhost/" + dbname;
			conn = DriverManager.getConnection(url, user, password);

			try {

				String sql = "SELECT IID FROM indices WHERE SYMBOL = \""
						+ symbol + "\"";
				Statement st = conn.createStatement();

				ResultSet rs = st.executeQuery(sql);

				while (rs.next()) {
					rs_iid = rs.getInt("IID");
				}

			} catch (SQLException ex) {
				System.err.println(ex.getMessage());
				return 0;
			}

			conn.close();
			return rs_iid;

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
			return 0;
		}
	}
}